--    Program: 		CAPARIMP_CUSTOMER_CONTACT.sql
--    Description:  Import AR CUSTOMER CONTACT information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         1-Feb-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp 			varchar2(2000);
   
   l_org_contact_id			number;
                    		                 		
   cursor cur_ar_customer_contact is
      select * from CRST_HZ_ORG_CONTACTS
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_ar_customer_contact loop   	
     l_err_msg_tmp := '';
     
     select HZ_ORG_CONTACTS_S.NEXTVAL 
     into l_org_contact_id
     from DUAL;
     
     update CRST_HZ_ORG_CONTACTS
	 set    org_contact_id_n = l_org_contact_id
	 where  org_contact_id = cur.org_contact_id;
	 
	 begin    
       -- import data
       insert into HZ_ORG_CONTACTS
       (
            ORG_CONTACT_ID             ,
            PARTY_RELATIONSHIP_ID      ,
            COMMENTS                   ,
            CONTACT_NUMBER             ,
            DEPARTMENT_CODE            ,
            DEPARTMENT                 ,
            TITLE                      ,
            JOB_TITLE                  ,
            MAIL_STOP                  ,
            CONTACT_KEY                ,
            DECISION_MAKER_FLAG        ,
            JOB_TITLE_CODE             ,
            MANAGED_BY                 ,
            REFERENCE_USE_FLAG         ,
            RANK                       ,
            LAST_UPDATE_DATE           ,
            LAST_UPDATED_BY            ,
            CREATION_DATE              ,
            CREATED_BY                 ,
            LAST_UPDATE_LOGIN          ,
            WH_UPDATE_DATE             ,
            REQUEST_ID                 ,
            PROGRAM_APPLICATION_ID     ,
            PROGRAM_ID                 ,
            PROGRAM_UPDATE_DATE        ,
            ATTRIBUTE_CATEGORY         ,
            ATTRIBUTE1                 ,
            ATTRIBUTE2                 ,
            ATTRIBUTE3                 ,
            ATTRIBUTE4                 ,
            ATTRIBUTE5                 ,
            ATTRIBUTE6                 ,
            ATTRIBUTE7                 ,
            ATTRIBUTE8                 ,
            ATTRIBUTE9                 ,
            ATTRIBUTE10                ,
            ATTRIBUTE11                ,
            ATTRIBUTE12                ,
            ATTRIBUTE13                ,
            ATTRIBUTE14                ,
            ATTRIBUTE15                ,
            ATTRIBUTE16                ,
            ATTRIBUTE17                ,
            ATTRIBUTE18                ,
            ATTRIBUTE19                ,
            ATTRIBUTE20                ,
            ATTRIBUTE21                ,
            ATTRIBUTE22                ,
            ATTRIBUTE23                ,
            ATTRIBUTE24                ,
            GLOBAL_ATTRIBUTE_CATEGORY  ,
            GLOBAL_ATTRIBUTE1          ,
            GLOBAL_ATTRIBUTE2          ,
            GLOBAL_ATTRIBUTE3          ,
            GLOBAL_ATTRIBUTE4          ,
            GLOBAL_ATTRIBUTE5          ,
            GLOBAL_ATTRIBUTE6          ,
            GLOBAL_ATTRIBUTE7          ,
            GLOBAL_ATTRIBUTE8          ,
            GLOBAL_ATTRIBUTE9          ,
            GLOBAL_ATTRIBUTE10         ,
            GLOBAL_ATTRIBUTE11         ,
            GLOBAL_ATTRIBUTE12         ,
            GLOBAL_ATTRIBUTE13         ,
            GLOBAL_ATTRIBUTE14         ,
            GLOBAL_ATTRIBUTE15         ,
            GLOBAL_ATTRIBUTE16         ,
            GLOBAL_ATTRIBUTE17         ,
            GLOBAL_ATTRIBUTE18         ,
            GLOBAL_ATTRIBUTE19         ,
            GLOBAL_ATTRIBUTE20         ,
            PARTY_SITE_ID              ,
            ORIG_SYSTEM_REFERENCE      ,
            NATIVE_LANGUAGE            ,
            OTHER_LANGUAGE_1           ,
            OTHER_LANGUAGE_2           ,
            MAILING_ADDRESS_ID         ,
            MATCH_GROUP_ID             ,
            STATUS                     ,
            OBJECT_VERSION_NUMBER      ,
            CREATED_BY_MODULE          ,
            APPLICATION_ID             
       )
       values
       (
            l_org_contact_id               ,
            CAPARIMP_MAPPING.GetHZRelationshipIDMapping(cur.PARTY_RELATIONSHIP_ID),
            cur.COMMENTS                   ,
            cur.CONTACT_NUMBER             ,
            cur.DEPARTMENT_CODE            ,
            cur.DEPARTMENT                 ,
            cur.TITLE                      ,
            cur.JOB_TITLE                  ,
            cur.MAIL_STOP                  ,
            cur.CONTACT_KEY                ,
            cur.DECISION_MAKER_FLAG        ,
            cur.JOB_TITLE_CODE             ,
            cur.MANAGED_BY                 ,
            cur.REFERENCE_USE_FLAG         ,
            cur.RANK                       ,
            cur.LAST_UPDATE_DATE           ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
            cur.CREATION_DATE              ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.LAST_UPDATE_LOGIN          ,
            cur.WH_UPDATE_DATE             ,
            cur.REQUEST_ID                 ,
            cur.PROGRAM_APPLICATION_ID     ,
            cur.PROGRAM_ID                 ,
            cur.PROGRAM_UPDATE_DATE        ,
            cur.ATTRIBUTE_CATEGORY         ,
            cur.ATTRIBUTE1                 ,
            cur.ATTRIBUTE2                 ,
            cur.ATTRIBUTE3                 ,
            cur.ATTRIBUTE4                 ,
            cur.ATTRIBUTE5                 ,
            cur.ATTRIBUTE6                 ,
            cur.ATTRIBUTE7                 ,
            cur.ATTRIBUTE8                 ,
            cur.ATTRIBUTE9                 ,
            cur.ATTRIBUTE10                ,
            cur.ATTRIBUTE11                ,
            cur.ATTRIBUTE12                ,
            cur.ATTRIBUTE13                ,
            cur.ATTRIBUTE14                ,
            cur.ATTRIBUTE15                ,
            cur.ATTRIBUTE16                ,
            cur.ATTRIBUTE17                ,
            cur.ATTRIBUTE18                ,
            cur.ATTRIBUTE19                ,
            cur.ATTRIBUTE20                ,
            cur.ATTRIBUTE21                ,
            cur.ATTRIBUTE22                ,
            cur.ATTRIBUTE23                ,
            cur.ATTRIBUTE24                ,
            cur.GLOBAL_ATTRIBUTE_CATEGORY  ,
            cur.GLOBAL_ATTRIBUTE1          ,
            cur.GLOBAL_ATTRIBUTE2          ,
            cur.GLOBAL_ATTRIBUTE3          ,
            cur.GLOBAL_ATTRIBUTE4          ,
            cur.GLOBAL_ATTRIBUTE5          ,
            cur.GLOBAL_ATTRIBUTE6          ,
            cur.GLOBAL_ATTRIBUTE7          ,
            cur.GLOBAL_ATTRIBUTE8          ,
            cur.GLOBAL_ATTRIBUTE9          ,
            cur.GLOBAL_ATTRIBUTE10         ,
            cur.GLOBAL_ATTRIBUTE11         ,
            cur.GLOBAL_ATTRIBUTE12         ,
            cur.GLOBAL_ATTRIBUTE13         ,
            cur.GLOBAL_ATTRIBUTE14         ,
            cur.GLOBAL_ATTRIBUTE15         ,
            cur.GLOBAL_ATTRIBUTE16         ,
            cur.GLOBAL_ATTRIBUTE17         ,
            cur.GLOBAL_ATTRIBUTE18         ,
            cur.GLOBAL_ATTRIBUTE19         ,
            cur.GLOBAL_ATTRIBUTE20         ,
            CAPARIMP_MAPPING.GetHZPartySiteIDMapping(cur.PARTY_SITE_ID),
            cur.ORIG_SYSTEM_REFERENCE      ,
            cur.NATIVE_LANGUAGE            ,
            cur.OTHER_LANGUAGE_1           ,
            cur.OTHER_LANGUAGE_2           ,
            cur.MAILING_ADDRESS_ID         ,
            cur.MATCH_GROUP_ID             ,
            cur.STATUS                     ,
            cur.OBJECT_VERSION_NUMBER      ,
            cur.CREATED_BY_MODULE          ,
            cur.APPLICATION_ID                         
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  	  
   end loop;
   
   if l_rejected_count <> 0 then
     commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_CUSTOMER_CONTACT', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
